import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.jfree.data.time.Month;
import org.jfree.data.time.TimeSeries;


public class StatisticHelper {

	public static List<TagUsage> getTagsUsage(){
		List<TagUsage> tu = new ArrayList<TagUsage>();
		Connection conn = DatabaseConnector.getConnection();
		if(conn != null){
			try {
				Statement stmt = conn.createStatement();
				
				String sql = "SELECT tag, COUNT(tag) AS tag_usage FROM tags GROUP BY tag ORDER BY tag_usage DESC LIMIT 10";
//				String sumsql = "SELECT SUM(T.tag_usage) AS tag_sum FROM (" + sql + ")T";				
//				ResultSet sumRs = stmt.executeQuery(sumsql);
//				double sum = 0;
//				if(sumRs.next()){
//					sum = sumRs.getDouble("tag_sum");
//				}
//				sumRs.close();
//				sumRs = null;
				
				ResultSet rs = stmt.executeQuery(sql);
				while(rs.next()){
					String name = rs.getString("tag");
					int usage = rs.getInt("tag_usage");
					//TagUsage e = new TagUsage(name, usage/sum);
					TagUsage e = new TagUsage(name, usage);
					tu.add(e);
				}
				
				rs.close();
				rs = null;
				stmt.close();
				stmt = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return tu;
	}
	
	public static List<TagUsage> getTagsUsage(int[] useridArry){
		List<TagUsage> tu = new ArrayList<TagUsage>();
		Connection conn = DatabaseConnector.getConnection();
		if(conn != null){
			try {
				Statement stmt = conn.createStatement();
				String sql1 = "SELECT tag, COUNT(tag) AS tag_usage FROM tags WHERE "; 
				String sql2 = "GROUP BY tag ORDER BY tag_usage DESC LIMIT 10";
				StringBuffer userids = new StringBuffer("tag_creator IN {");
				for(int i = 0; i < useridArry.length; i++){
					userids.append(useridArry[i] + ", ");
				}
				int length = userids.length();
				userids.delete(length-2, length);
				userids.append("} ");
				String sql = sql1 + userids + sql2;
				
				ResultSet rs = stmt.executeQuery(sql);
				while(rs.next()){
					String name = rs.getString("tag");
					int usage = rs.getInt("tag_usage");
					TagUsage e = new TagUsage(name, usage);
					tu.add(e);
				}
				
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return tu;
	}
	
	public static List<TagUsage> getTagsUsage(String userid){
		List<TagUsage> tu = new ArrayList<TagUsage>();
		Connection conn = DatabaseConnector.getConnection();
		if(conn != null){
			try {
				PreparedStatement stmt = conn.prepareStatement("SELECT tag, COUNT(tag) AS tag_usage FROM tags WHERE tag_creator =? GROUP BY tag ORDER BY tag_usage DESC LIMIT 10");
                stmt.setString(1, userid);
                ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					String name = rs.getString("tag");
					int usage = rs.getInt("tag_usage");
					TagUsage e = new TagUsage(name, usage);
					tu.add(e);
				}
				
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return tu;
	}
	
	public static List<TagUsage> getTagsUsage(Date start, Date end){
		SimpleDateFormat format =
            new SimpleDateFormat("yyyy-MM-dd");
		List<TagUsage> tu = new ArrayList<TagUsage>();
		Connection conn = DatabaseConnector.getConnection();
		if(conn != null){
			try {
				Statement stmt = conn.createStatement();
				StringBuffer sql = new StringBuffer();
				sql.append("SELECT t.tag AS tag, COUNT(t.tag) AS tag_usage FROM images i INNER JOIN tags t ON i.image_id = t.image_id WHERE i.cdate >= '");
				sql.append(format.format(start));
				sql.append("' and i.cdate <= '");
				sql.append(format.format(end));  
				sql.append("' GROUP BY t.tag ORDER BY tag_usage DESC LIMIT 10");
				
				ResultSet rs = stmt.executeQuery(sql.toString());
				while(rs.next()){
					String name = rs.getString("tag");
					int usage = rs.getInt("tag_usage");
					TagUsage e = new TagUsage(name, usage);
					tu.add(e);
				}
				
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return tu;
	}
	
	public static TimeSeries getImageSeries(){
		TimeSeries series = new TimeSeries("TimeLine");
		Connection conn = DatabaseConnector.getConnection();
		if(conn != null){
			try {
				Statement stmt = conn.createStatement();
				
				String sql = "SELECT COUNT( image_id ) AS IMAGE_COUNT, YEAR( cdate ) AS IMAGE_YEAR, MONTH( cdate ) AS IMAGE_MONTH FROM images GROUP BY YEAR( cdate ) , MONTH( cdate )";
				ResultSet rs = stmt.executeQuery(sql);
				while(rs.next()){
					double usage = rs.getDouble("IMAGE_COUNT");
					int year = rs.getInt("IMAGE_YEAR");
					int month = rs.getInt("IMAGE_MONTH");
					if (month >= 1 && month <= 12) {
						series.add(new Month(month, year), usage);
			        }				
				}
				
				rs.close();
				rs = null;
				stmt.close();
				stmt = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return series;
	}
	
	public static TimeSeries getImageSeries(Date start, Date end){
		SimpleDateFormat format =
            new SimpleDateFormat("yyyy-MM-dd");

		TimeSeries series = new TimeSeries("TimeLine");
		Connection conn = DatabaseConnector.getConnection();
		if(conn != null){
			try {
				Statement stmt = conn.createStatement();
				StringBuffer sql = new StringBuffer();
				sql.append("SELECT COUNT( image_id ) AS IMAGE_COUNT, YEAR( cdate ) AS IMAGE_YEAR, MONTH( cdate ) AS IMAGE_MONTH FROM images ");
				sql.append("WHERE cdate >=  '");
				sql.append(format.format(start));
				sql.append("' AND cdate <= '");
				sql.append(format.format(end));
				sql.append("' GROUP BY YEAR( cdate ) , MONTH( cdate )");
				ResultSet rs = stmt.executeQuery(sql.toString());
				while(rs.next()){
					double usage = rs.getDouble("IMAGE_COUNT");
					int year = rs.getInt("IMAGE_YEAR");
					int month = rs.getInt("IMAGE_MONTH");
					if (month >= 1 && month <= 12) {
						series.add(new Month(month, year), usage);
			        }					
				}
				
				rs.close();
				rs = null;
				stmt.close();
				stmt = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return series;
	}
	
}
